/*
Navicat SQL Server Data Transfer

Source Server         : WIN-5EN9K24594E
Source Server Version : 90000
Source Host           : WIN-5EN9K24594E\SQLEXPRESS:1433
Source Database       : cse132b
Source Schema         : dbo

Target Server Type    : SQL Server
Target Server Version : 90000
File Encoding         : 65001

Date: 2012-03-15 19:43:11
*/


-- ----------------------------
-- Table structure for [dbo].[account]
-- ----------------------------
DROP TABLE [dbo].[account]
GO
CREATE TABLE [dbo].[account] (
[username] varchar(50) NOT NULL ,
[password] varchar(50) NULL ,
[role] varchar(50) NULL 
)


GO

-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO [dbo].[account] ([username], [password], [role]) VALUES (N'alind', N'ChangeMe', N'faculty');
GO
INSERT INTO [dbo].[account] ([username], [password], [role]) VALUES (N'c2chen', N'ChangeMe', N'student');
GO
INSERT INTO [dbo].[account] ([username], [password], [role]) VALUES (N'cchen', N'ChangeMe', N'student');
GO
INSERT INTO [dbo].[account] ([username], [password], [role]) VALUES (N'cwei', N'ChangeMe', N'student');
GO
INSERT INTO [dbo].[account] ([username], [password], [role]) VALUES (N'kevinw', N'ChangeMe', N'student');
GO
INSERT INTO [dbo].[account] ([username], [password], [role]) VALUES (N'test', N'ChangeMe', N'student');
GO
INSERT INTO [dbo].[account] ([username], [password], [role]) VALUES (N'vvianu', N'ChangeMe', N'faculty');
GO
INSERT INTO [dbo].[account] ([username], [password], [role]) VALUES (N'yannisp', N'ChangeMe', N'faculty');
GO

-- ----------------------------
-- Table structure for [dbo].[advice]
-- ----------------------------
DROP TABLE [dbo].[advice]
GO
CREATE TABLE [dbo].[advice] (
[student_id] varchar(50) NOT NULL ,
[faculty_name] varchar(50) NOT NULL 
)


GO

-- ----------------------------
-- Records of advice
-- ----------------------------
INSERT INTO [dbo].[advice] ([student_id], [faculty_name]) VALUES (N'A50000002', N'Alin Deutsch');
GO
INSERT INTO [dbo].[advice] ([student_id], [faculty_name]) VALUES (N'A50000002', N'Yannis Papakonstantinou');
GO
INSERT INTO [dbo].[advice] ([student_id], [faculty_name]) VALUES (N'A50000003', N'Yannis Papakonstantinou');
GO
INSERT INTO [dbo].[advice] ([student_id], [faculty_name]) VALUES (N'A50000004', N'Alin Deutsch');
GO
INSERT INTO [dbo].[advice] ([student_id], [faculty_name]) VALUES (N'A50000004', N'Victor Vianu');
GO
INSERT INTO [dbo].[advice] ([student_id], [faculty_name]) VALUES (N'A50000004', N'Yannis Papakonstantinou');
GO

-- ----------------------------
-- Table structure for [dbo].[attendance]
-- ----------------------------
DROP TABLE [dbo].[attendance]
GO
CREATE TABLE [dbo].[attendance] (
[attendance_id] int NOT NULL IDENTITY(1,1) ,
[student_id] varchar(50) NOT NULL ,
[start_date] varchar(20) NULL ,
[end_date] varchar(20) NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[attendance]', RESEED, 5)
GO

-- ----------------------------
-- Records of attendance
-- ----------------------------
SET IDENTITY_INSERT [dbo].[attendance] ON
GO
INSERT INTO [dbo].[attendance] ([attendance_id], [student_id], [start_date], [end_date]) VALUES (N'2', N'A50000001', N'09-10-2010', N'06-01-2011');
GO
INSERT INTO [dbo].[attendance] ([attendance_id], [student_id], [start_date], [end_date]) VALUES (N'3', N'A50000001', N'09-20-2011', N'03-23-2012');
GO
INSERT INTO [dbo].[attendance] ([attendance_id], [student_id], [start_date], [end_date]) VALUES (N'5', N'A50000002', N'09-01-2010', N'05-01-2012');
GO
SET IDENTITY_INSERT [dbo].[attendance] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[category]
-- ----------------------------
DROP TABLE [dbo].[category]
GO
CREATE TABLE [dbo].[category] (
[category_id] int NOT NULL IDENTITY(1,1) ,
[requirement_id] int NOT NULL ,
[category_name] varchar(50) NOT NULL ,
[min_units] int NULL ,
[min_avg_grade] varchar(50) NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[category]', RESEED, 5)
GO

-- ----------------------------
-- Records of category
-- ----------------------------
SET IDENTITY_INSERT [dbo].[category] ON
GO
INSERT INTO [dbo].[category] ([category_id], [requirement_id], [category_name], [min_units], [min_avg_grade]) VALUES (N'1', N'4', N'Science', N'120', N'3.0');
GO
INSERT INTO [dbo].[category] ([category_id], [requirement_id], [category_name], [min_units], [min_avg_grade]) VALUES (N'3', N'4', N'Project', N'45', N'4.0');
GO
INSERT INTO [dbo].[category] ([category_id], [requirement_id], [category_name], [min_units], [min_avg_grade]) VALUES (N'4', N'4', N'Upper division', N'60', N'3.0');
GO
INSERT INTO [dbo].[category] ([category_id], [requirement_id], [category_name], [min_units], [min_avg_grade]) VALUES (N'5', N'4', N'Engineering', N'40', N'B');
GO
SET IDENTITY_INSERT [dbo].[category] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[category_course]
-- ----------------------------
DROP TABLE [dbo].[category_course]
GO
CREATE TABLE [dbo].[category_course] (
[category_id] int NOT NULL ,
[course_number] varchar(50) NOT NULL 
)


GO

-- ----------------------------
-- Records of category_course
-- ----------------------------
INSERT INTO [dbo].[category_course] ([category_id], [course_number]) VALUES (N'1', N'CSE101');
GO
INSERT INTO [dbo].[category_course] ([category_id], [course_number]) VALUES (N'3', N'CSE293');
GO
INSERT INTO [dbo].[category_course] ([category_id], [course_number]) VALUES (N'4', N'CSE100');
GO
INSERT INTO [dbo].[category_course] ([category_id], [course_number]) VALUES (N'4', N'CSE101');
GO
INSERT INTO [dbo].[category_course] ([category_id], [course_number]) VALUES (N'4', N'CSE132A');
GO
INSERT INTO [dbo].[category_course] ([category_id], [course_number]) VALUES (N'4', N'CSE132B');
GO
INSERT INTO [dbo].[category_course] ([category_id], [course_number]) VALUES (N'5', N'CSE132A');
GO
INSERT INTO [dbo].[category_course] ([category_id], [course_number]) VALUES (N'5', N'CSE132B');
GO

-- ----------------------------
-- Table structure for [dbo].[class]
-- ----------------------------
DROP TABLE [dbo].[class]
GO
CREATE TABLE [dbo].[class] (
[class_id] int NOT NULL IDENTITY(1,1) ,
[course_number] varchar(50) NOT NULL ,
[title] varchar(50) NULL ,
[quarter] varchar(50) NULL ,
[year] int NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[class]', RESEED, 12)
GO

-- ----------------------------
-- Records of class
-- ----------------------------
SET IDENTITY_INSERT [dbo].[class] ON
GO
INSERT INTO [dbo].[class] ([class_id], [course_number], [title], [quarter], [year]) VALUES (N'1', N'CSE100', N'Data Structure', N'Spring', N'2009');
GO
INSERT INTO [dbo].[class] ([class_id], [course_number], [title], [quarter], [year]) VALUES (N'2', N'CSE100', N'Advanced Data Structure', N'Fall', N'2007');
GO
INSERT INTO [dbo].[class] ([class_id], [course_number], [title], [quarter], [year]) VALUES (N'3', N'CSE132B', N'Database Application', N'Spring', N'2009');
GO
INSERT INTO [dbo].[class] ([class_id], [course_number], [title], [quarter], [year]) VALUES (N'4', N'CSE132A', N'Database Principles', N'Spring', N'2009');
GO
INSERT INTO [dbo].[class] ([class_id], [course_number], [title], [quarter], [year]) VALUES (N'5', N'CSE100', N'Data Structure', N'Summer', N'2008');
GO
INSERT INTO [dbo].[class] ([class_id], [course_number], [title], [quarter], [year]) VALUES (N'6', N'CSE293', N'Research', N'Spring', N'2009');
GO
INSERT INTO [dbo].[class] ([class_id], [course_number], [title], [quarter], [year]) VALUES (N'7', N'CSE101', N'Algorithm', N'Spring', N'2009');
GO
INSERT INTO [dbo].[class] ([class_id], [course_number], [title], [quarter], [year]) VALUES (N'8', N'CSE132B', N'Database Application', N'Fall', N'2008');
GO
INSERT INTO [dbo].[class] ([class_id], [course_number], [title], [quarter], [year]) VALUES (N'9', N'CSE132A', N'Database Principles', N'Spring', N'2005');
GO
INSERT INTO [dbo].[class] ([class_id], [course_number], [title], [quarter], [year]) VALUES (N'10', N'CSE132A', N'Database Principles', N'Spring', N'2010');
GO
INSERT INTO [dbo].[class] ([class_id], [course_number], [title], [quarter], [year]) VALUES (N'11', N'CSE293', N'Research', N'Summer', N'2012');
GO
INSERT INTO [dbo].[class] ([class_id], [course_number], [title], [quarter], [year]) VALUES (N'12', N'CSE101', N'Algorithm', N'Summer', N'2010');
GO
SET IDENTITY_INSERT [dbo].[class] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[committee]
-- ----------------------------
DROP TABLE [dbo].[committee]
GO
CREATE TABLE [dbo].[committee] (
[student_id] varchar(50) NOT NULL ,
[faculty_name] varchar(50) NOT NULL 
)


GO

-- ----------------------------
-- Records of committee
-- ----------------------------
INSERT INTO [dbo].[committee] ([student_id], [faculty_name]) VALUES (N'A50000002', N'Alin Deutsch');
GO
INSERT INTO [dbo].[committee] ([student_id], [faculty_name]) VALUES (N'A50000002', N'Victor Vianu');
GO
INSERT INTO [dbo].[committee] ([student_id], [faculty_name]) VALUES (N'A50000002', N'Yannis Papakonstantinou');
GO

-- ----------------------------
-- Table structure for [dbo].[concentration]
-- ----------------------------
DROP TABLE [dbo].[concentration]
GO
CREATE TABLE [dbo].[concentration] (
[concentration_id] int NOT NULL IDENTITY(1,1) ,
[requirement_id] int NOT NULL ,
[topic] varchar(50) NOT NULL ,
[min_units] int NOT NULL ,
[min_avg_grade] varchar(10) NOT NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[concentration]', RESEED, 5)
GO

-- ----------------------------
-- Records of concentration
-- ----------------------------
SET IDENTITY_INSERT [dbo].[concentration] ON
GO
INSERT INTO [dbo].[concentration] ([concentration_id], [requirement_id], [topic], [min_units], [min_avg_grade]) VALUES (N'3', N'2', N'Database', N'8', N'B');
GO
INSERT INTO [dbo].[concentration] ([concentration_id], [requirement_id], [topic], [min_units], [min_avg_grade]) VALUES (N'4', N'2', N'Algorithm', N'4', N'B');
GO
INSERT INTO [dbo].[concentration] ([concentration_id], [requirement_id], [topic], [min_units], [min_avg_grade]) VALUES (N'5', N'2', N'Project', N'12', N'B');
GO
SET IDENTITY_INSERT [dbo].[concentration] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[concentration_course]
-- ----------------------------
DROP TABLE [dbo].[concentration_course]
GO
CREATE TABLE [dbo].[concentration_course] (
[concentration_id] int NOT NULL ,
[course_number] varchar(50) NOT NULL 
)


GO

-- ----------------------------
-- Records of concentration_course
-- ----------------------------
INSERT INTO [dbo].[concentration_course] ([concentration_id], [course_number]) VALUES (N'3', N'CSE132A');
GO
INSERT INTO [dbo].[concentration_course] ([concentration_id], [course_number]) VALUES (N'3', N'CSE132B');
GO
INSERT INTO [dbo].[concentration_course] ([concentration_id], [course_number]) VALUES (N'3', N'CSE293');
GO
INSERT INTO [dbo].[concentration_course] ([concentration_id], [course_number]) VALUES (N'4', N'CSE100');
GO
INSERT INTO [dbo].[concentration_course] ([concentration_id], [course_number]) VALUES (N'4', N'CSE101');
GO
INSERT INTO [dbo].[concentration_course] ([concentration_id], [course_number]) VALUES (N'5', N'CSE293');
GO

-- ----------------------------
-- Table structure for [dbo].[course]
-- ----------------------------
DROP TABLE [dbo].[course]
GO
CREATE TABLE [dbo].[course] (
[course_number] varchar(50) NOT NULL ,
[lab_required] varchar(3) NULL ,
[letter_grade_allowed] varchar(3) NULL ,
[su_allowed] varchar(3) NULL ,
[min_units] int NULL ,
[max_units] int NULL 
)


GO

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO [dbo].[course] ([course_number], [lab_required], [letter_grade_allowed], [su_allowed], [min_units], [max_units]) VALUES (N'CSE100', N'NO', N'YES', N'NO', N'2', N'4');
GO
INSERT INTO [dbo].[course] ([course_number], [lab_required], [letter_grade_allowed], [su_allowed], [min_units], [max_units]) VALUES (N'CSE101', N'NO', N'YES', N'YES', N'4', N'4');
GO
INSERT INTO [dbo].[course] ([course_number], [lab_required], [letter_grade_allowed], [su_allowed], [min_units], [max_units]) VALUES (N'CSE132A', N'NO', N'YES', N'YES', N'4', N'4');
GO
INSERT INTO [dbo].[course] ([course_number], [lab_required], [letter_grade_allowed], [su_allowed], [min_units], [max_units]) VALUES (N'CSE132B', N'NO', N'YES', N'YES', N'4', N'4');
GO
INSERT INTO [dbo].[course] ([course_number], [lab_required], [letter_grade_allowed], [su_allowed], [min_units], [max_units]) VALUES (N'CSE293', N'NO', N'NO', N'YES', N'1', N'12');
GO

-- ----------------------------
-- Table structure for [dbo].[CPG]
-- ----------------------------
DROP TABLE [dbo].[CPG]
GO
CREATE TABLE [dbo].[CPG] (
[course_number] varchar(10) NULL ,
[faculty_name] varchar(50) NULL ,
[grade] varchar(10) NULL ,
[count_grade] int NULL 
)


GO

-- ----------------------------
-- Records of CPG
-- ----------------------------
INSERT INTO [dbo].[CPG] ([course_number], [faculty_name], [grade], [count_grade]) VALUES (N'CSE100', N'Alin Deutsch', N'A', N'1');
GO
INSERT INTO [dbo].[CPG] ([course_number], [faculty_name], [grade], [count_grade]) VALUES (N'CSE132B', N'Alin Deutsch', N'A', N'1');
GO
INSERT INTO [dbo].[CPG] ([course_number], [faculty_name], [grade], [count_grade]) VALUES (N'CSE100', N'Victor Vianu', N'A', N'2');
GO
INSERT INTO [dbo].[CPG] ([course_number], [faculty_name], [grade], [count_grade]) VALUES (N'CSE100', N'Alin Deutsch', N'B', N'1');
GO
INSERT INTO [dbo].[CPG] ([course_number], [faculty_name], [grade], [count_grade]) VALUES (N'CSE132A', N'Alin Deutsch', N'B', N'2');
GO
INSERT INTO [dbo].[CPG] ([course_number], [faculty_name], [grade], [count_grade]) VALUES (N'CSE101', N'Alin Deutsch', N'D', N'1');
GO
INSERT INTO [dbo].[CPG] ([course_number], [faculty_name], [grade], [count_grade]) VALUES (N'CSE101', N'Alin Deutsch', N'other', N'1');
GO
INSERT INTO [dbo].[CPG] ([course_number], [faculty_name], [grade], [count_grade]) VALUES (N'CSE132A', N'Victor Vianu', N'other', N'1');
GO
INSERT INTO [dbo].[CPG] ([course_number], [faculty_name], [grade], [count_grade]) VALUES (N'CSE293', N'Victor Vianu', N'other', N'2');
GO
INSERT INTO [dbo].[CPG] ([course_number], [faculty_name], [grade], [count_grade]) VALUES (N'CSE100', N'Yannis Papakonstantinou', N'other', N'2');
GO

-- ----------------------------
-- Table structure for [dbo].[CPQG]
-- ----------------------------
DROP TABLE [dbo].[CPQG]
GO
CREATE TABLE [dbo].[CPQG] (
[course_number] varchar(10) NULL ,
[faculty_name] varchar(50) NULL ,
[quarter] varchar(10) NULL ,
[year] int NULL ,
[grade] varchar(10) NULL ,
[count_grade] int NULL 
)


GO

-- ----------------------------
-- Records of CPQG
-- ----------------------------
INSERT INTO [dbo].[CPQG] ([course_number], [faculty_name], [quarter], [year], [grade], [count_grade]) VALUES (N'CSE100', N'Victor Vianu', N'Fall', N'2007', N'A', N'2');
GO
INSERT INTO [dbo].[CPQG] ([course_number], [faculty_name], [quarter], [year], [grade], [count_grade]) VALUES (N'CSE100', N'Alin Deutsch', N'Summer', N'2008', N'A', N'1');
GO
INSERT INTO [dbo].[CPQG] ([course_number], [faculty_name], [quarter], [year], [grade], [count_grade]) VALUES (N'CSE132B', N'Alin Deutsch', N'Fall', N'2008', N'A', N'1');
GO
INSERT INTO [dbo].[CPQG] ([course_number], [faculty_name], [quarter], [year], [grade], [count_grade]) VALUES (N'CSE100', N'Alin Deutsch', N'Summer', N'2008', N'B', N'1');
GO
INSERT INTO [dbo].[CPQG] ([course_number], [faculty_name], [quarter], [year], [grade], [count_grade]) VALUES (N'CSE132A', N'Alin Deutsch', N'Spring', N'2005', N'B', N'2');
GO
INSERT INTO [dbo].[CPQG] ([course_number], [faculty_name], [quarter], [year], [grade], [count_grade]) VALUES (N'CSE101', N'Alin Deutsch', N'Summer', N'2010', N'D', N'1');
GO
INSERT INTO [dbo].[CPQG] ([course_number], [faculty_name], [quarter], [year], [grade], [count_grade]) VALUES (N'CSE100', N'Yannis Papakonstantinou', N'Spring', N'2009', N'other', N'2');
GO
INSERT INTO [dbo].[CPQG] ([course_number], [faculty_name], [quarter], [year], [grade], [count_grade]) VALUES (N'CSE101', N'Alin Deutsch', N'Spring', N'2009', N'other', N'1');
GO
INSERT INTO [dbo].[CPQG] ([course_number], [faculty_name], [quarter], [year], [grade], [count_grade]) VALUES (N'CSE132A', N'Victor Vianu', N'Spring', N'2009', N'other', N'1');
GO
INSERT INTO [dbo].[CPQG] ([course_number], [faculty_name], [quarter], [year], [grade], [count_grade]) VALUES (N'CSE293', N'Victor Vianu', N'Spring', N'2009', N'other', N'2');
GO

-- ----------------------------
-- Table structure for [dbo].[degree]
-- ----------------------------
DROP TABLE [dbo].[degree]
GO
CREATE TABLE [dbo].[degree] (
[degree_id] int NOT NULL IDENTITY(1,1) ,
[student_id] varchar(50) NOT NULL ,
[field_of_study] varchar(50) NULL ,
[level] varchar(50) NULL ,
[university] varchar(50) NULL ,
[year] int NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[degree]', RESEED, 4)
GO

-- ----------------------------
-- Records of degree
-- ----------------------------
SET IDENTITY_INSERT [dbo].[degree] ON
GO
INSERT INTO [dbo].[degree] ([degree_id], [student_id], [field_of_study], [level], [university], [year]) VALUES (N'1', N'A50000001', N'Computer Science and Technology', N'B.S.', N'Beihang University', N'2010');
GO
INSERT INTO [dbo].[degree] ([degree_id], [student_id], [field_of_study], [level], [university], [year]) VALUES (N'2', N'A50000001', N'Computer Science', N'M.S.', N'University of California, San Diego', N'2012');
GO
INSERT INTO [dbo].[degree] ([degree_id], [student_id], [field_of_study], [level], [university], [year]) VALUES (N'4', N'A50000002', N'Automation', N'B.S.', N'Shanghai Jiao Tong University', N'2010');
GO
SET IDENTITY_INSERT [dbo].[degree] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[degree_requirement]
-- ----------------------------
DROP TABLE [dbo].[degree_requirement]
GO
CREATE TABLE [dbo].[degree_requirement] (
[requirement_id] int NOT NULL IDENTITY(1,1) ,
[title] varchar(50) NULL ,
[department] varchar(50) NULL ,
[min_units] int NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[degree_requirement]', RESEED, 9)
GO

-- ----------------------------
-- Records of degree_requirement
-- ----------------------------
SET IDENTITY_INSERT [dbo].[degree_requirement] ON
GO
INSERT INTO [dbo].[degree_requirement] ([requirement_id], [title], [department], [min_units]) VALUES (N'2', N'MS', N'Computer Science and Engineering', N'49');
GO
INSERT INTO [dbo].[degree_requirement] ([requirement_id], [title], [department], [min_units]) VALUES (N'3', N'MA', N'Bioengineering', N'48');
GO
INSERT INTO [dbo].[degree_requirement] ([requirement_id], [title], [department], [min_units]) VALUES (N'4', N'BSC', N'Electrical and Computer Engineering', N'100');
GO
INSERT INTO [dbo].[degree_requirement] ([requirement_id], [title], [department], [min_units]) VALUES (N'5', N'BA', N'NanoEngineering', N'120');
GO
INSERT INTO [dbo].[degree_requirement] ([requirement_id], [title], [department], [min_units]) VALUES (N'6', N'PhD', N'Computer Science and Engineering', N'36');
GO
INSERT INTO [dbo].[degree_requirement] ([requirement_id], [title], [department], [min_units]) VALUES (N'8', N'BSC', N'Computer Science and Engineering', N'110');
GO
INSERT INTO [dbo].[degree_requirement] ([requirement_id], [title], [department], [min_units]) VALUES (N'9', N'MS', N'Electrical and Computer Engineering', N'90');
GO
SET IDENTITY_INSERT [dbo].[degree_requirement] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[department]
-- ----------------------------
DROP TABLE [dbo].[department]
GO
CREATE TABLE [dbo].[department] (
[department] varchar(50) NOT NULL 
)


GO

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO [dbo].[department] ([department]) VALUES (N'Bioengineering');
GO
INSERT INTO [dbo].[department] ([department]) VALUES (N'Computer Science and Engineering');
GO
INSERT INTO [dbo].[department] ([department]) VALUES (N'Electrical and Computer Engineering');
GO
INSERT INTO [dbo].[department] ([department]) VALUES (N'Mechanical and Aerospace Engineering');
GO
INSERT INTO [dbo].[department] ([department]) VALUES (N'NanoEngineering');
GO
INSERT INTO [dbo].[department] ([department]) VALUES (N'Structural Engineering');
GO

-- ----------------------------
-- Table structure for [dbo].[enroll]
-- ----------------------------
DROP TABLE [dbo].[enroll]
GO
CREATE TABLE [dbo].[enroll] (
[section_id] int NOT NULL ,
[student_id] varchar(50) NOT NULL ,
[grade_type] varchar(50) NULL ,
[units] int NULL ,
[is_waitlisted] varchar(3) NULL ,
[grade] varchar(10) NULL 
)


GO

-- ----------------------------
-- Records of enroll
-- ----------------------------
INSERT INTO [dbo].[enroll] ([section_id], [student_id], [grade_type], [units], [is_waitlisted], [grade]) VALUES (N'3', N'A50000001', N'letter', N'4', N'NO', N'');
GO
INSERT INTO [dbo].[enroll] ([section_id], [student_id], [grade_type], [units], [is_waitlisted], [grade]) VALUES (N'3', N'A50000003', N'letter', N'4', N'NO', N'');
GO
INSERT INTO [dbo].[enroll] ([section_id], [student_id], [grade_type], [units], [is_waitlisted], [grade]) VALUES (N'4', N'A50000001', N'letter', N'2', N'', N'A');
GO
INSERT INTO [dbo].[enroll] ([section_id], [student_id], [grade_type], [units], [is_waitlisted], [grade]) VALUES (N'4', N'A50000002', N'letter', N'2', N'', N'A-');
GO
INSERT INTO [dbo].[enroll] ([section_id], [student_id], [grade_type], [units], [is_waitlisted], [grade]) VALUES (N'6', N'A50000003', N'letter', N'4', N'NO', N'');
GO
INSERT INTO [dbo].[enroll] ([section_id], [student_id], [grade_type], [units], [is_waitlisted], [grade]) VALUES (N'8', N'A50000001', N'letter', N'2', N'', N'B');
GO
INSERT INTO [dbo].[enroll] ([section_id], [student_id], [grade_type], [units], [is_waitlisted], [grade]) VALUES (N'8', N'A50000002', N'letter', N'4', N'', N'A-');
GO
INSERT INTO [dbo].[enroll] ([section_id], [student_id], [grade_type], [units], [is_waitlisted], [grade]) VALUES (N'10', N'A50000001', N'letter', N'4', N'NO', N'');
GO
INSERT INTO [dbo].[enroll] ([section_id], [student_id], [grade_type], [units], [is_waitlisted], [grade]) VALUES (N'11', N'A50000001', N'S/U', N'1', N'NO', N'');
GO
INSERT INTO [dbo].[enroll] ([section_id], [student_id], [grade_type], [units], [is_waitlisted], [grade]) VALUES (N'11', N'A50000002', N'S/U', N'1', N'NO', N'');
GO
INSERT INTO [dbo].[enroll] ([section_id], [student_id], [grade_type], [units], [is_waitlisted], [grade]) VALUES (N'13', N'A50000001', N'letter', N'4', N'', N'A');
GO
INSERT INTO [dbo].[enroll] ([section_id], [student_id], [grade_type], [units], [is_waitlisted], [grade]) VALUES (N'14', N'A50000001', N'letter', N'4', N'', N'D');
GO
INSERT INTO [dbo].[enroll] ([section_id], [student_id], [grade_type], [units], [is_waitlisted], [grade]) VALUES (N'15', N'A50000001', N'letter', N'4', N'', N'B-');
GO
INSERT INTO [dbo].[enroll] ([section_id], [student_id], [grade_type], [units], [is_waitlisted], [grade]) VALUES (N'15', N'A50000002', N'letter', N'4', N'', N'B+');
GO

-- ----------------------------
-- Table structure for [dbo].[faculty]
-- ----------------------------
DROP TABLE [dbo].[faculty]
GO
CREATE TABLE [dbo].[faculty] (
[name] varchar(50) NOT NULL ,
[title] varchar(50) NULL ,
[department] varchar(50) NULL ,
[username] varchar(50) NOT NULL 
)


GO

-- ----------------------------
-- Records of faculty
-- ----------------------------
INSERT INTO [dbo].[faculty] ([name], [title], [department], [username]) VALUES (N'Alin Deutsch', N'Associate Professor', N'Computer Science and Engineering', N'alind');
GO
INSERT INTO [dbo].[faculty] ([name], [title], [department], [username]) VALUES (N'Victor Vianu', N'Professor', N'Computer Science and Engineering', N'vvianu');
GO
INSERT INTO [dbo].[faculty] ([name], [title], [department], [username]) VALUES (N'Yannis Papakonstantinou', N'Professor', N'Computer Science and Engineering', N'yannisp');
GO

-- ----------------------------
-- Table structure for [dbo].[grade_conversion]
-- ----------------------------
DROP TABLE [dbo].[grade_conversion]
GO
CREATE TABLE [dbo].[grade_conversion] (
[letter_grade] varchar(2) NOT NULL ,
[number_grade] float(53) NOT NULL 
)


GO

-- ----------------------------
-- Records of grade_conversion
-- ----------------------------
INSERT INTO [dbo].[grade_conversion] ([letter_grade], [number_grade]) VALUES (N'A', N'4');
GO
INSERT INTO [dbo].[grade_conversion] ([letter_grade], [number_grade]) VALUES (N'A-', N'3.7');
GO
INSERT INTO [dbo].[grade_conversion] ([letter_grade], [number_grade]) VALUES (N'A+', N'4.3');
GO
INSERT INTO [dbo].[grade_conversion] ([letter_grade], [number_grade]) VALUES (N'B', N'3.1');
GO
INSERT INTO [dbo].[grade_conversion] ([letter_grade], [number_grade]) VALUES (N'B-', N'2.8');
GO
INSERT INTO [dbo].[grade_conversion] ([letter_grade], [number_grade]) VALUES (N'B+', N'3.4');
GO
INSERT INTO [dbo].[grade_conversion] ([letter_grade], [number_grade]) VALUES (N'C', N'2.2');
GO
INSERT INTO [dbo].[grade_conversion] ([letter_grade], [number_grade]) VALUES (N'C-', N'1.9');
GO
INSERT INTO [dbo].[grade_conversion] ([letter_grade], [number_grade]) VALUES (N'C+', N'2.5');
GO
INSERT INTO [dbo].[grade_conversion] ([letter_grade], [number_grade]) VALUES (N'D', N'1.6');
GO
INSERT INTO [dbo].[grade_conversion] ([letter_grade], [number_grade]) VALUES (N'F', N'0');
GO

-- ----------------------------
-- Table structure for [dbo].[grade_merge]
-- ----------------------------
DROP TABLE [dbo].[grade_merge]
GO
CREATE TABLE [dbo].[grade_merge] (
[original_grade] varchar(5) NOT NULL ,
[target_grade] varchar(10) NOT NULL 
)


GO

-- ----------------------------
-- Records of grade_merge
-- ----------------------------
INSERT INTO [dbo].[grade_merge] ([original_grade], [target_grade]) VALUES (N'A+', N'A');
GO
INSERT INTO [dbo].[grade_merge] ([original_grade], [target_grade]) VALUES (N'A', N'A');
GO
INSERT INTO [dbo].[grade_merge] ([original_grade], [target_grade]) VALUES (N'A-', N'A');
GO
INSERT INTO [dbo].[grade_merge] ([original_grade], [target_grade]) VALUES (N'B+', N'B');
GO
INSERT INTO [dbo].[grade_merge] ([original_grade], [target_grade]) VALUES (N'B', N'B');
GO
INSERT INTO [dbo].[grade_merge] ([original_grade], [target_grade]) VALUES (N'B-', N'B');
GO
INSERT INTO [dbo].[grade_merge] ([original_grade], [target_grade]) VALUES (N'C+', N'C');
GO
INSERT INTO [dbo].[grade_merge] ([original_grade], [target_grade]) VALUES (N'C', N'C');
GO
INSERT INTO [dbo].[grade_merge] ([original_grade], [target_grade]) VALUES (N'C-', N'C');
GO
INSERT INTO [dbo].[grade_merge] ([original_grade], [target_grade]) VALUES (N'D', N'D');
GO

-- ----------------------------
-- Table structure for [dbo].[meeting]
-- ----------------------------
DROP TABLE [dbo].[meeting]
GO
CREATE TABLE [dbo].[meeting] (
[meeting_id] int NOT NULL IDENTITY(1,1) ,
[section_id] int NOT NULL ,
[type] varchar(50) NULL ,
[is_mandatory] varchar(3) NULL ,
[start_time] int NULL ,
[end_time] int NULL ,
[weekday] varchar(50) NULL ,
[location] varchar(50) NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[meeting]', RESEED, 27)
GO

-- ----------------------------
-- Records of meeting
-- ----------------------------
SET IDENTITY_INSERT [dbo].[meeting] ON
GO
INSERT INTO [dbo].[meeting] ([meeting_id], [section_id], [type], [is_mandatory], [start_time], [end_time], [weekday], [location]) VALUES (N'3', N'3', N'LE', N'YES', N'1400', N'1520', N'M', N'Center 109');
GO
INSERT INTO [dbo].[meeting] ([meeting_id], [section_id], [type], [is_mandatory], [start_time], [end_time], [weekday], [location]) VALUES (N'4', N'12', N'LE', N'YES', N'1600', N'1820', N'M', N'Center 214');
GO
INSERT INTO [dbo].[meeting] ([meeting_id], [section_id], [type], [is_mandatory], [start_time], [end_time], [weekday], [location]) VALUES (N'5', N'10', N'LE', N'YES', N'1530', N'1700', N'Tu', N'Center 109');
GO
INSERT INTO [dbo].[meeting] ([meeting_id], [section_id], [type], [is_mandatory], [start_time], [end_time], [weekday], [location]) VALUES (N'6', N'6', N'LE', N'YES', N'1500', N'1630', N'Th', N'Center 214');
GO
INSERT INTO [dbo].[meeting] ([meeting_id], [section_id], [type], [is_mandatory], [start_time], [end_time], [weekday], [location]) VALUES (N'7', N'13', N'LE', N'YES', N'1100', N'1230', N'Th', N'Peterson 103');
GO
INSERT INTO [dbo].[meeting] ([meeting_id], [section_id], [type], [is_mandatory], [start_time], [end_time], [weekday], [location]) VALUES (N'8', N'4', N'LAB', N'YES', N'1400', N'1520', N'M', N'Peterson 103');
GO
SET IDENTITY_INSERT [dbo].[meeting] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[ms_student]
-- ----------------------------
DROP TABLE [dbo].[ms_student]
GO
CREATE TABLE [dbo].[ms_student] (
[student_id] varchar(50) NOT NULL ,
[in_5_year_class] varchar(3) NULL ,
[department] varchar(50) NULL 
)


GO

-- ----------------------------
-- Records of ms_student
-- ----------------------------
INSERT INTO [dbo].[ms_student] ([student_id], [in_5_year_class], [department]) VALUES (N'A50000002', N'NO', N'Computer Science and Engineering');
GO
INSERT INTO [dbo].[ms_student] ([student_id], [in_5_year_class], [department]) VALUES (N'A50000004', N'YES', N'Computer Science and Engineering');
GO

-- ----------------------------
-- Table structure for [dbo].[phd_student]
-- ----------------------------
DROP TABLE [dbo].[phd_student]
GO
CREATE TABLE [dbo].[phd_student] (
[student_id] varchar(50) NOT NULL ,
[has_candidacy] varchar(3) NULL ,
[department] varchar(50) NULL 
)


GO

-- ----------------------------
-- Records of phd_student
-- ----------------------------
INSERT INTO [dbo].[phd_student] ([student_id], [has_candidacy], [department]) VALUES (N'A50000003', N'YES', N'Electrical and Computer Engineering');
GO

-- ----------------------------
-- Table structure for [dbo].[prerequisite]
-- ----------------------------
DROP TABLE [dbo].[prerequisite]
GO
CREATE TABLE [dbo].[prerequisite] (
[course_number] varchar(50) NOT NULL ,
[prerequisite_number] varchar(50) NOT NULL 
)


GO

-- ----------------------------
-- Records of prerequisite
-- ----------------------------
INSERT INTO [dbo].[prerequisite] ([course_number], [prerequisite_number]) VALUES (N'CSE101', N'CSE100');
GO
INSERT INTO [dbo].[prerequisite] ([course_number], [prerequisite_number]) VALUES (N'CSE132A', N'CSE100');
GO
INSERT INTO [dbo].[prerequisite] ([course_number], [prerequisite_number]) VALUES (N'CSE132B', N'CSE100');
GO
INSERT INTO [dbo].[prerequisite] ([course_number], [prerequisite_number]) VALUES (N'CSE132B', N'CSE132A');
GO

-- ----------------------------
-- Table structure for [dbo].[probation]
-- ----------------------------
DROP TABLE [dbo].[probation]
GO
CREATE TABLE [dbo].[probation] (
[probation_id] int NOT NULL IDENTITY(1,1) ,
[student_id] varchar(50) NOT NULL ,
[start_date] varchar(20) NULL ,
[end_date] varchar(20) NULL ,
[reason] varchar(50) NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[probation]', RESEED, 2)
GO

-- ----------------------------
-- Records of probation
-- ----------------------------
SET IDENTITY_INSERT [dbo].[probation] ON
GO
INSERT INTO [dbo].[probation] ([probation_id], [student_id], [start_date], [end_date], [reason]) VALUES (N'2', N'A50000003', N'02-01-2012', N'06-01-2012', N'Cheating in final.');
GO
SET IDENTITY_INSERT [dbo].[probation] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[review_session]
-- ----------------------------
DROP TABLE [dbo].[review_session]
GO
CREATE TABLE [dbo].[review_session] (
[review_session_id] int NOT NULL IDENTITY(1,1) ,
[section_id] int NOT NULL ,
[date_time] datetime NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[review_session]', RESEED, 2)
GO

-- ----------------------------
-- Records of review_session
-- ----------------------------
SET IDENTITY_INSERT [dbo].[review_session] ON
GO
INSERT INTO [dbo].[review_session] ([review_session_id], [section_id], [date_time]) VALUES (N'2', N'1', N'2012-03-12 17:00:00.000');
GO
SET IDENTITY_INSERT [dbo].[review_session] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[section]
-- ----------------------------
DROP TABLE [dbo].[section]
GO
CREATE TABLE [dbo].[section] (
[section_id] int NOT NULL IDENTITY(1,1) ,
[class_id] int NOT NULL ,
[limit] int NULL ,
[faculty_name] varchar(50) NOT NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[section]', RESEED, 15)
GO

-- ----------------------------
-- Records of section
-- ----------------------------
SET IDENTITY_INSERT [dbo].[section] ON
GO
INSERT INTO [dbo].[section] ([section_id], [class_id], [limit], [faculty_name]) VALUES (N'1', N'3', N'30', N'Alin Deutsch');
GO
INSERT INTO [dbo].[section] ([section_id], [class_id], [limit], [faculty_name]) VALUES (N'3', N'1', N'20', N'Yannis Papakonstantinou');
GO
INSERT INTO [dbo].[section] ([section_id], [class_id], [limit], [faculty_name]) VALUES (N'4', N'2', N'30', N'Victor Vianu');
GO
INSERT INTO [dbo].[section] ([section_id], [class_id], [limit], [faculty_name]) VALUES (N'6', N'4', N'150', N'Victor Vianu');
GO
INSERT INTO [dbo].[section] ([section_id], [class_id], [limit], [faculty_name]) VALUES (N'8', N'5', N'40', N'Alin Deutsch');
GO
INSERT INTO [dbo].[section] ([section_id], [class_id], [limit], [faculty_name]) VALUES (N'10', N'7', N'40', N'Alin Deutsch');
GO
INSERT INTO [dbo].[section] ([section_id], [class_id], [limit], [faculty_name]) VALUES (N'11', N'6', N'2', N'Victor Vianu');
GO
INSERT INTO [dbo].[section] ([section_id], [class_id], [limit], [faculty_name]) VALUES (N'12', N'1', N'30', N'Yannis Papakonstantinou');
GO
INSERT INTO [dbo].[section] ([section_id], [class_id], [limit], [faculty_name]) VALUES (N'13', N'8', N'100', N'Alin Deutsch');
GO
INSERT INTO [dbo].[section] ([section_id], [class_id], [limit], [faculty_name]) VALUES (N'14', N'12', N'10', N'Alin Deutsch');
GO
INSERT INTO [dbo].[section] ([section_id], [class_id], [limit], [faculty_name]) VALUES (N'15', N'9', N'30', N'Alin Deutsch');
GO
SET IDENTITY_INSERT [dbo].[section] OFF
GO

-- ----------------------------
-- Table structure for [dbo].[student]
-- ----------------------------
DROP TABLE [dbo].[student]
GO
CREATE TABLE [dbo].[student] (
[student_id] varchar(50) NOT NULL ,
[residency] varchar(50) NULL ,
[first_name] varchar(50) NULL ,
[middle_name] varchar(50) NULL ,
[last_name] varchar(50) NULL ,
[ssn] int NULL ,
[enrollment_status] varchar(3) NULL ,
[username] varchar(50) NOT NULL 
)


GO

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO [dbo].[student] ([student_id], [residency], [first_name], [middle_name], [last_name], [ssn], [enrollment_status], [username]) VALUES (N'A50000001', N'foreign', N'Chao', N'', N'Wei', N'111111111', N'YES', N'cwei');
GO
INSERT INTO [dbo].[student] ([student_id], [residency], [first_name], [middle_name], [last_name], [ssn], [enrollment_status], [username]) VALUES (N'A50000002', N'California', N'Changkun', N'', N'Chen', N'999999999', N'NO', N'cchen');
GO
INSERT INTO [dbo].[student] ([student_id], [residency], [first_name], [middle_name], [last_name], [ssn], [enrollment_status], [username]) VALUES (N'A50000003', N'California', N'Test', N'T.', N'Test', N'111111111', N'NO', N'test');
GO
INSERT INTO [dbo].[student] ([student_id], [residency], [first_name], [middle_name], [last_name], [ssn], [enrollment_status], [username]) VALUES (N'A50000004', N'non-CA', N'Kevin', N'C.', N'Wei', N'777777777', N'YES', N'kevinw');
GO
INSERT INTO [dbo].[student] ([student_id], [residency], [first_name], [middle_name], [last_name], [ssn], [enrollment_status], [username]) VALUES (N'A50000007', N'foreign', N'Daryl ', N'', N'Chen', N'333333333', N'YES', N'c2chen');
GO

-- ----------------------------
-- Table structure for [dbo].[student_enrollment]
-- ----------------------------
DROP TABLE [dbo].[student_enrollment]
GO
CREATE TABLE [dbo].[student_enrollment] (
[student_id] varchar(50) NOT NULL ,
[quarter] varchar(10) NOT NULL ,
[year] int NOT NULL 
)


GO

-- ----------------------------
-- Records of student_enrollment
-- ----------------------------
INSERT INTO [dbo].[student_enrollment] ([student_id], [quarter], [year]) VALUES (N'A50000001', N'Spring', N'2009');
GO
INSERT INTO [dbo].[student_enrollment] ([student_id], [quarter], [year]) VALUES (N'A50000002', N'Spring', N'2009');
GO
INSERT INTO [dbo].[student_enrollment] ([student_id], [quarter], [year]) VALUES (N'A50000004', N'Spring', N'2009');
GO

-- ----------------------------
-- Table structure for [dbo].[undergrad_student]
-- ----------------------------
DROP TABLE [dbo].[undergrad_student]
GO
CREATE TABLE [dbo].[undergrad_student] (
[student_id] varchar(50) NOT NULL ,
[college] varchar(50) NULL ,
[major] varchar(50) NULL ,
[minor] varchar(50) NULL 
)


GO

-- ----------------------------
-- Records of undergrad_student
-- ----------------------------
INSERT INTO [dbo].[undergrad_student] ([student_id], [college], [major], [minor]) VALUES (N'A50000001', N'Warren', N'Computer Science', N'');
GO
INSERT INTO [dbo].[undergrad_student] ([student_id], [college], [major], [minor]) VALUES (N'A50000007', N'Revelle', N'Electrical Engineering', N'');
GO

-- ----------------------------
-- View structure for [dbo].[grade]
-- ----------------------------
DROP VIEW [dbo].[grade]
GO
CREATE VIEW [dbo].[grade] AS 
SELECT course_number, faculty_name, quarter, year, grade, count(*) AS count_grade 
FROM class, section, enroll 
WHERE class.class_id = section.class_id
AND section.section_id = enroll.section_id
GROUP BY course_number, quarter, year, faculty_name, grade
GO

-- ----------------------------
-- Indexes structure for table account
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[account]
-- ----------------------------
ALTER TABLE [dbo].[account] ADD PRIMARY KEY ([username])
GO

-- ----------------------------
-- Indexes structure for table advice
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[advice]
-- ----------------------------
ALTER TABLE [dbo].[advice] ADD PRIMARY KEY ([student_id], [faculty_name])
GO

-- ----------------------------
-- Indexes structure for table attendance
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[attendance]
-- ----------------------------
ALTER TABLE [dbo].[attendance] ADD PRIMARY KEY ([attendance_id])
GO

-- ----------------------------
-- Indexes structure for table category
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[category]
-- ----------------------------
ALTER TABLE [dbo].[category] ADD PRIMARY KEY ([category_id])
GO

-- ----------------------------
-- Indexes structure for table category_course
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[category_course]
-- ----------------------------
ALTER TABLE [dbo].[category_course] ADD PRIMARY KEY ([category_id], [course_number])
GO

-- ----------------------------
-- Indexes structure for table class
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[class]
-- ----------------------------
ALTER TABLE [dbo].[class] ADD PRIMARY KEY ([class_id])
GO

-- ----------------------------
-- Indexes structure for table committee
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[committee]
-- ----------------------------
ALTER TABLE [dbo].[committee] ADD PRIMARY KEY ([student_id], [faculty_name])
GO

-- ----------------------------
-- Indexes structure for table concentration
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[concentration]
-- ----------------------------
ALTER TABLE [dbo].[concentration] ADD PRIMARY KEY ([concentration_id])
GO

-- ----------------------------
-- Indexes structure for table concentration_course
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[concentration_course]
-- ----------------------------
ALTER TABLE [dbo].[concentration_course] ADD PRIMARY KEY ([concentration_id], [course_number])
GO

-- ----------------------------
-- Indexes structure for table course
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[course]
-- ----------------------------
ALTER TABLE [dbo].[course] ADD PRIMARY KEY ([course_number])
GO

-- ----------------------------
-- Indexes structure for table degree
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[degree]
-- ----------------------------
ALTER TABLE [dbo].[degree] ADD PRIMARY KEY ([degree_id])
GO

-- ----------------------------
-- Indexes structure for table degree_requirement
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[degree_requirement]
-- ----------------------------
ALTER TABLE [dbo].[degree_requirement] ADD PRIMARY KEY ([requirement_id])
GO

-- ----------------------------
-- Indexes structure for table department
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[department]
-- ----------------------------
ALTER TABLE [dbo].[department] ADD PRIMARY KEY ([department])
GO

-- ----------------------------
-- Indexes structure for table enroll
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[enroll]
-- ----------------------------
ALTER TABLE [dbo].[enroll] ADD PRIMARY KEY ([section_id], [student_id])
GO

-- ----------------------------
-- Triggers structure for table [dbo].[enroll]
-- ----------------------------
DROP TRIGGER [dbo].[enrollmentLimit]
GO
CREATE TRIGGER [dbo].[enrollmentLimit]
ON [dbo].[enroll]
AFTER INSERT
AS
BEGIN
DECLARE @enrollCount INTEGER
DECLARE @limit INTEGER
DECLARE @insertedID INTEGER  

SELECT @insertedID = (SELECT section_id FROM inserted)
SELECT @enrollCount = (SELECT count(*) FROM enroll WHERE enroll.section_id = @insertedID)
SELECT @limit = (SELECT limit FROM section WHERE section.section_id = @insertedID)
IF (@enrollCount > @limit)
	BEGIN
		DECLARE @newStudent VARCHAR(50)
		SELECT @newStudent = (SELECT student_id FROM inserted)
		DELETE FROM enroll WHERE section_id = @insertedID AND student_id = @newStudent
		RAISERROR ('Sorry, the section is already full.',12, 1)
	END
END

GO
DROP TRIGGER [dbo].[trig_grade]
GO
CREATE TRIGGER [dbo].[trig_grade]
ON [dbo].[enroll]
AFTER INSERT
AS
BEGIN
	DECLARE @section_id INTEGER
	DECLARE @student_id VARCHAR(20)
	DECLARE @original_grade VARCHAR(10)
	DECLARE @target_grade VARCHAR(10)
	DECLARE @course_number VARCHAR(10)
	DECLARE @class_id INTEGER
	DECLARE @faculty_name VARCHAR(50)
	DECLARE @quarter VARCHAR(20)
	DECLARE @year INTEGER

	SELECT @section_id = (SELECT section_id FROM inserted)
	SELECT @student_id = (SELECT student_id FROM inserted)
	SELECT @original_grade = (SELECT grade FROM inserted)
	SELECT @target_grade = (SELECT target_grade FROM grade_merge
														WHERE original_grade = @original_grade)
	SELECT @class_id = (SELECT class_id FROM section WHERE section_id = @section_id)
	SELECT @course_number = (SELECT course_number FROM class 
													WHERE class_id = @class_id)
	SELECT @faculty_name = (SELECT faculty_name FROM section 
													WHERE section_id = @section_id)
	SELECT @quarter = (SELECT quarter FROM class 
													WHERE class_id = @class_id)
	SELECT @year = (SELECT year FROM class 
													WHERE class_id = @class_id)


IF EXISTS (SELECT grade FROM inserted)
	BEGIN
		IF EXISTS (SELECT * FROM CPQG WHERE course_number = @course_number
								AND faculty_name = @faculty_name
								AND quarter = @quarter
								AND year = @year
								AND grade = @target_grade)
			BEGIN
				IF EXISTS (SELECT * FROM grade_merge WHERE original_grade = @original_grade)
					BEGIN
						UPDATE CPQG SET count_grade = count_grade + 1
								WHERE course_number = @course_number
								AND faculty_name = @faculty_name
								AND quarter = @quarter
								AND year = @year
								AND grade = @target_grade
					END
				ELSE
					BEGIN
						UPDATE CPQG SET count_grade = count_grade + 1
								WHERE course_number = @course_number
								AND faculty_name = @faculty_name
								AND quarter = @quarter
								AND year = @year
								AND grade = 'other'
					END	
			END
		ELSE
			BEGIN
				IF EXISTS (SELECT * FROM grade_merge WHERE original_grade = @original_grade)
					BEGIN
						INSERT INTO CPQG VALUES (@course_number, @faculty_name, @quarter, @year, @target_grade, 1)
					END
				ELSE
					BEGIN
						INSERT INTO CPQG VALUES (@course_number, @faculty_name, @quarter, @year, 'other', 1)	
					END	
			END

		IF EXISTS (SELECT * FROM CPG WHERE course_number = @course_number
								AND faculty_name = @faculty_name
								AND grade = @target_grade)
			BEGIN
				IF EXISTS (SELECT * FROM grade_merge WHERE original_grade = @original_grade)
					BEGIN
						UPDATE CPG SET count_grade = count_grade + 1
								WHERE course_number = @course_number
								AND faculty_name = @faculty_name
								AND grade = @target_grade
					END
				ELSE
					BEGIN
						UPDATE CPG SET count_grade = count_grade + 1
								WHERE course_number = @course_number
								AND faculty_name = @faculty_name
								AND grade = 'other'
					END	
			END
		ELSE
			BEGIN
				IF EXISTS (SELECT * FROM grade_merge WHERE original_grade = @original_grade)
					BEGIN
						INSERT INTO CPG VALUES (@course_number, @faculty_name, @target_grade, 1)
					END
				ELSE
					BEGIN
						INSERT INTO CPG VALUES (@course_number, @faculty_name, 'other', 1)	
					END	
			END
		
	END
END

GO

-- ----------------------------
-- Indexes structure for table faculty
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[faculty]
-- ----------------------------
ALTER TABLE [dbo].[faculty] ADD PRIMARY KEY ([name])
GO

-- ----------------------------
-- Indexes structure for table grade_conversion
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[grade_conversion]
-- ----------------------------
ALTER TABLE [dbo].[grade_conversion] ADD PRIMARY KEY ([letter_grade])
GO

-- ----------------------------
-- Indexes structure for table meeting
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[meeting]
-- ----------------------------
ALTER TABLE [dbo].[meeting] ADD PRIMARY KEY ([meeting_id])
GO

-- ----------------------------
-- Triggers structure for table [dbo].[meeting]
-- ----------------------------
DROP TRIGGER [dbo].[trig_meeting]
GO
CREATE TRIGGER [dbo].[trig_meeting]
ON [dbo].[meeting]
AFTER INSERT, UPDATE
AS
BEGIN
DECLARE @start_time INTEGER
DECLARE @end_time INTEGER  
DECLARE @section_id INTEGER
DECLARE @meeting_id INTEGER
DECLARE @weekday VARCHAR

SELECT @start_time = (SELECT start_time FROM inserted)
SELECT @end_time = (SELECT end_time FROM inserted)
SELECT @section_id = (SELECT section_id FROM inserted)
SELECT @meeting_id = (SELECT meeting_id FROM inserted)
SELECT @weekday = (SELECT weekday FROM inserted)

IF EXISTS (SELECT * FROM meeting WHERE meeting.section_id = @section_id 
						AND meeting.weekday = @weekday
						AND @end_time > start_time AND @start_time < end_time 
						AND NOT @meeting_id = meeting_id)
	BEGIN
		ROLLBACK TRANSACTION
		RAISERROR ('Sorry, Conflict meeting time.',12, 1)
	END
END

GO
DROP TRIGGER [dbo].[trig_professor2]
GO
CREATE TRIGGER [dbo].[trig_professor2]
ON [dbo].[meeting]
AFTER INSERT, UPDATE
AS
BEGIN
DECLARE @faculty_name VARCHAR(50)
DECLARE @section_id INTEGER
DECLARE @start_time INTEGER  
DECLARE @end_time INTEGER  
DECLARE @meeting_id INTEGER
DECLARE @weekday VARCHAR(20)
DECLARE @quarter VARCHAR(20)
DECLARE @year INTEGER
DECLARE @class_id INTEGER


SELECT @section_id = (SELECT section_id FROM inserted)
SELECT @start_time = (SELECT start_time FROM inserted)
SELECT @end_time = (SELECT end_time FROM inserted)
SELECT @weekday = (SELECT weekday FROM inserted)
SELECT @meeting_id = (SELECT meeting_id FROM inserted)

SELECT @faculty_name = (SELECT faculty_name FROM section WHERE section.section_id = @section_id)
SELECT @class_id = (SELECT class_id FROM section WHERE section_id = @section_id)
SELECT @quarter = (SELECT quarter FROM class WHERE class.class_id = @class_id)
SELECT @year = (SELECT year FROM class WHERE class.class_id = @class_id)


IF EXISTS (SELECT * FROM class,section,meeting WHERE faculty_name = @faculty_name 
						AND class.class_id = section.class_id
						AND class.quarter = @quarter
						AND class.year = @year
						AND section.section_id = meeting.section_id
						AND meeting.weekday = @weekday
						AND @end_time > start_time AND @start_time < end_time 
						AND NOT @meeting_id = meeting_id
						AND type = 'LE')
	BEGIN
		ROLLBACK TRANSACTION
		RAISERROR ('Sorry, Conflict section for the professor.',12, 1)
	END
END

GO

-- ----------------------------
-- Indexes structure for table ms_student
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[ms_student]
-- ----------------------------
ALTER TABLE [dbo].[ms_student] ADD PRIMARY KEY ([student_id])
GO

-- ----------------------------
-- Indexes structure for table phd_student
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[phd_student]
-- ----------------------------
ALTER TABLE [dbo].[phd_student] ADD PRIMARY KEY ([student_id])
GO

-- ----------------------------
-- Indexes structure for table prerequisite
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[prerequisite]
-- ----------------------------
ALTER TABLE [dbo].[prerequisite] ADD PRIMARY KEY ([course_number], [prerequisite_number])
GO

-- ----------------------------
-- Indexes structure for table probation
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[probation]
-- ----------------------------
ALTER TABLE [dbo].[probation] ADD PRIMARY KEY ([probation_id])
GO

-- ----------------------------
-- Indexes structure for table review_session
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[review_session]
-- ----------------------------
ALTER TABLE [dbo].[review_session] ADD PRIMARY KEY ([review_session_id])
GO

-- ----------------------------
-- Indexes structure for table section
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[section]
-- ----------------------------
ALTER TABLE [dbo].[section] ADD PRIMARY KEY ([section_id])
GO

-- ----------------------------
-- Triggers structure for table [dbo].[section]
-- ----------------------------
DROP TRIGGER [dbo].[trig_professor]
GO
CREATE TRIGGER [dbo].[trig_professor]
ON [dbo].[section]
AFTER UPDATE
AS
BEGIN
DECLARE @faculty_name VARCHAR(50)
DECLARE @section_id INTEGER
DECLARE @start_time INTEGER  
DECLARE @end_time INTEGER  
DECLARE @meeting_id INTEGER
DECLARE @weekday VARCHAR(20)
DECLARE @quarter VARCHAR(20)
DECLARE @year INTEGER
DECLARE @class_id INTEGER

SELECT @faculty_name = (SELECT faculty_name FROM inserted)
SELECT @section_id = (SELECT section_id FROM inserted)
SELECT @class_id = (SELECT class_id FROM section WHERE section_id = @section_id)
SELECT @quarter = (SELECT quarter FROM class WHERE class.class_id = @class_id)
SELECT @year = (SELECT year FROM class WHERE class.class_id = @class_id)

SELECT @meeting_id = (SELECT meeting_id FROM meeting WHERE section_id = @section_id
												AND type = 'LE')
SELECT @start_time = (SELECT start_time FROM meeting WHERE section_id = @section_id
												AND type = 'LE')
SELECT @end_time = (SELECT end_time FROM meeting WHERE section_id = @section_id
												AND type = 'LE')
SELECT @weekday = (SELECT weekday FROM meeting WHERE section_id = @section_id
												AND type = 'LE')

IF EXISTS (SELECT * FROM class,section,meeting WHERE faculty_name = @faculty_name 
						AND class.class_id = section.class_id
						AND class.quarter = @quarter
						AND class.year = @year
						AND section.section_id = meeting.section_id
						AND meeting.weekday = @weekday
						AND @end_time > start_time AND @start_time < end_time 
						AND NOT @meeting_id = meeting_id
						AND type = 'LE')
	BEGIN
		ROLLBACK TRANSACTION
		RAISERROR ('Sorry, Conflict section for the professor.',12, 1)
	END
END

GO

-- ----------------------------
-- Indexes structure for table student
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[student]
-- ----------------------------
ALTER TABLE [dbo].[student] ADD PRIMARY KEY ([student_id])
GO

-- ----------------------------
-- Indexes structure for table student_enrollment
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[student_enrollment]
-- ----------------------------
ALTER TABLE [dbo].[student_enrollment] ADD PRIMARY KEY ([student_id], [quarter], [year])
GO

-- ----------------------------
-- Indexes structure for table undergrad_student
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[undergrad_student]
-- ----------------------------
ALTER TABLE [dbo].[undergrad_student] ADD PRIMARY KEY ([student_id])
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[advice]
-- ----------------------------
ALTER TABLE [dbo].[advice] ADD FOREIGN KEY ([faculty_name]) REFERENCES [dbo].[faculty] ([name]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[advice] ADD FOREIGN KEY ([student_id]) REFERENCES [dbo].[student] ([student_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[attendance]
-- ----------------------------
ALTER TABLE [dbo].[attendance] ADD FOREIGN KEY ([student_id]) REFERENCES [dbo].[student] ([student_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[category]
-- ----------------------------
ALTER TABLE [dbo].[category] ADD FOREIGN KEY ([requirement_id]) REFERENCES [dbo].[degree_requirement] ([requirement_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[category_course]
-- ----------------------------
ALTER TABLE [dbo].[category_course] ADD FOREIGN KEY ([category_id]) REFERENCES [dbo].[category] ([category_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[category_course] ADD FOREIGN KEY ([course_number]) REFERENCES [dbo].[course] ([course_number]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[class]
-- ----------------------------
ALTER TABLE [dbo].[class] ADD FOREIGN KEY ([course_number]) REFERENCES [dbo].[course] ([course_number]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[committee]
-- ----------------------------
ALTER TABLE [dbo].[committee] ADD FOREIGN KEY ([faculty_name]) REFERENCES [dbo].[faculty] ([name]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[committee] ADD FOREIGN KEY ([student_id]) REFERENCES [dbo].[student] ([student_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[concentration]
-- ----------------------------
ALTER TABLE [dbo].[concentration] ADD FOREIGN KEY ([requirement_id]) REFERENCES [dbo].[degree_requirement] ([requirement_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[concentration_course]
-- ----------------------------
ALTER TABLE [dbo].[concentration_course] ADD FOREIGN KEY ([concentration_id]) REFERENCES [dbo].[concentration] ([concentration_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[concentration_course] ADD FOREIGN KEY ([course_number]) REFERENCES [dbo].[course] ([course_number]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[degree]
-- ----------------------------
ALTER TABLE [dbo].[degree] ADD FOREIGN KEY ([student_id]) REFERENCES [dbo].[student] ([student_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[enroll]
-- ----------------------------
ALTER TABLE [dbo].[enroll] ADD FOREIGN KEY ([section_id]) REFERENCES [dbo].[section] ([section_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[enroll] ADD FOREIGN KEY ([student_id]) REFERENCES [dbo].[student] ([student_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[faculty]
-- ----------------------------
ALTER TABLE [dbo].[faculty] ADD FOREIGN KEY ([department]) REFERENCES [dbo].[department] ([department]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[faculty] ADD FOREIGN KEY ([username]) REFERENCES [dbo].[account] ([username]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[meeting]
-- ----------------------------
ALTER TABLE [dbo].[meeting] ADD FOREIGN KEY ([section_id]) REFERENCES [dbo].[section] ([section_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[ms_student]
-- ----------------------------
ALTER TABLE [dbo].[ms_student] ADD FOREIGN KEY ([department]) REFERENCES [dbo].[department] ([department]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[ms_student] ADD FOREIGN KEY ([student_id]) REFERENCES [dbo].[student] ([student_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[phd_student]
-- ----------------------------
ALTER TABLE [dbo].[phd_student] ADD FOREIGN KEY ([department]) REFERENCES [dbo].[department] ([department]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[phd_student] ADD FOREIGN KEY ([student_id]) REFERENCES [dbo].[student] ([student_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[prerequisite]
-- ----------------------------
ALTER TABLE [dbo].[prerequisite] ADD FOREIGN KEY ([course_number]) REFERENCES [dbo].[course] ([course_number]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[prerequisite] ADD FOREIGN KEY ([prerequisite_number]) REFERENCES [dbo].[course] ([course_number]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[probation]
-- ----------------------------
ALTER TABLE [dbo].[probation] ADD FOREIGN KEY ([student_id]) REFERENCES [dbo].[student] ([student_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[review_session]
-- ----------------------------
ALTER TABLE [dbo].[review_session] ADD FOREIGN KEY ([section_id]) REFERENCES [dbo].[section] ([section_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[section]
-- ----------------------------
ALTER TABLE [dbo].[section] ADD FOREIGN KEY ([class_id]) REFERENCES [dbo].[class] ([class_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[section] ADD FOREIGN KEY ([faculty_name]) REFERENCES [dbo].[faculty] ([name]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[student]
-- ----------------------------
ALTER TABLE [dbo].[student] ADD FOREIGN KEY ([username]) REFERENCES [dbo].[account] ([username]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[student] ADD FOREIGN KEY ([username]) REFERENCES [dbo].[account] ([username]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[student] ADD FOREIGN KEY ([username]) REFERENCES [dbo].[account] ([username]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[student_enrollment]
-- ----------------------------
ALTER TABLE [dbo].[student_enrollment] ADD FOREIGN KEY ([student_id]) REFERENCES [dbo].[student] ([student_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[undergrad_student]
-- ----------------------------
ALTER TABLE [dbo].[undergrad_student] ADD FOREIGN KEY ([student_id]) REFERENCES [dbo].[student] ([student_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
